if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InjectInfoEdBudgetFeed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InjectInfoEdBudgetFeed]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			InjectInfoEdBudgetFeed
* Purpose:		
*				
*
* SCRAPED DATA		Coded
* -------------		-----
* Cost Center		
* Sponsor
* Fund
*
*
*
* 
* 
* 
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
Create PROCEDURE [dbo].[InjectInfoEdBudgetFeed]
AS
BEGIN

	SET NOCOUNT ON		

	BEGIN TRY

	BEGIN TRANSACTION InjectInfoEdBudgetFeed	
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		I N S E R T  C O N T A I N E R  V I E W
		Insert the AgrBudgetContainerView table for a new budget.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
--print 0	
	SET IDENTITY_INSERT AgrBudgetContainer ON
	

	INSERT	AgrBudgetContainer
				(Id, Name, 
				Description,AgrBudgetTemplateTypeId,
				IndirectCostBaseId)
	SELECT	AgrBudgetContainerId, Name, 
			Description, AgrBudgetTemplateTypeId,
			IndirectCostBaseId
	FROM	stg_IE_AgrBudgetContainer_Ctl 

	SET IDENTITY_INSERT AgrBudgetContainer OFF



--print 1
	SET IDENTITY_INSERT AgrBudgetContainerView ON
	

	INSERT	AgrBudgetContainerView
				(Id, AgrBudgetContainerId, 
				AgrBudgetViewTypeId, TargetPatientNumber,
				PerCapitaValue,InflationRate,
				CurrentGrantYear)
	SELECT	AgrBudgetContainerViewId, AgrBudgetContainerId, 
			AgrBudgetViewTypeId, TargetPatientNumber,
			PerCapitaValue, InflationRate,
			[Year]
	FROM	stg_IE_AgrBudgetContainerView_Ctl  --Stg_IE_AgrBudget_Ctl 
	

	SET IDENTITY_INSERT AgrBudgetContainerView OFF



	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		I N S E R T  B U D G E T  H E A D E R  R E C O R D
		Insert the AgrBudget table for a new budget.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
--print 2


	SET IDENTITY_INSERT AgrBudget ON
	
	INSERT	AgrBudget
			(Id, AgrBudgetTypeId, 
			StartDate, EndDate,
			Year,IsAwarded,
			AgrBudgetContainerViewId, AgrFundAgreeMapId,
			IsAdjustment
			)
	SELECT	
			AgrBudgetId, AgrBudgetTypeId, 
			AgrBudgetStartDate, AgrBudgetEndDate,
			Year, IsAwarded,
			AgrBudgetContainerViewId, AgrFundAgrAgreementMapId,
			IsAdjustment
	FROM	Stg_IE_AgrBudget_Ctl 
	WHERE	
			IsValid = 1 
			and IsNewBudget = 1			


	SET IDENTITY_INSERT AgrBudget OFF
	
		
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		I N S E R T  A G R B U D G E T I T E M  T A B L E		
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
--print 5
	
	INSERT	AgrBudgetItem
			(AgrBudgetId, 
			AgrContainerAccountTemplateMapId,
			ItemValue			
			)
	SELECT	
			SABI.AgrBudgetId, 
			SABI.AgrContainerAccountTemplateMapId,
			SABI.AgrBudgetItemValue
	FROM	Stg_IE_AgrBudgetInfo_Ctl SABI
			inner join Stg_IE_AgrBudget_Ctl SAB on SAB.nBudgetId = SABI.nBudgetId
	WHERE	
			SABI.IsValid = 1 
			and SABI.IsNewRecord = 1	
			and SAB.IsValid = 1			

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		I N S E R T  A G R B U D G E T I C R A T E  T A B L E		
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
--print 6	
		
	INSERT	AgrBudgetICRate
			(AgrBudgetId,
			Rate, NumberOfMonths,
			SequenceNo, EffectiveDate)
	SELECT	SABIC.AgrBudgetId,		 
			isnull(SABIC.Rate,0), SABIC.NumberOfMonths,
			SABIC.SequenceNo, SABIC.EffectiveDate
	FROM
			Stg_IE_AgrBudgetICRate SABIC
			inner join Stg_IE_AgrBudget_Ctl SAB on SAB.nBudgetId = SABIC.nBudgetId
	WHERE
			SAB.IsValid = 1
			AND SABIC.IsNewRecord = 1			

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		I N S E R T  S U B C O N T R A C T O R M A P  R E C O R D
		Insert the AgrAgreement_SubContractor_Map table.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
--print 6.5
	
	INSERT	AgrAgreement_SubContractor_Map
			(AgrAgreementId, 
			AgrSubContractorId,
			AgrBudgetContainerId,
			FinAccountId
			)
	SELECT				
			AgrAgreementId, AgrSubContractorId, 
			AgrBudgetContainerId, FinAccountId
	FROM	Stg_IE_AgrAgreementSubcontractor_Map
	WHERE	
			IsValid = 1 
			and IsNewSubContractor = 1
	
		
	/*
	**************************************
	* C H A N G E D  B U D G E T S		 *
	**************************************
	*/
--print 7
	
	Update	AB
	Set
		AgrBudgetTypeId = SAB.AgrBudgetTypeId, 
		StartDate = SAB.AgrBudgetStartDate,
		EndDate = SAB.AgrBudgetEndDate,
		Year = SAB.Year,
		IsAwarded = SAB.IsAwarded,
		AgrFundAgreeMapId = SAB.AgrFundAgrAgreementMapId,
		IsAdjustment = SAB.IsAdjustment
	FROM	
		Stg_IE_AgrBudget_Ctl  SAB
		INNER JOIN AgrBudget AB on AB.Id = SAB.AgrBudgetId 
	where 
		SAB.IsValid = 1 
		and SAB.IsNewBudget = 0	

--print 8

	Update	ABCV
	Set
		TargetPatientNumber = SAB.TargetPatientNumber,
		PerCapitaValue = SAB.PerCapitaValue,
		InflationRate = SAB.InflationRate,
		CurrentGrantYear = SAB.CurrentGrantYear
	FROM	
		Stg_IE_AgrBudget_Ctl  SAB
		INNER JOIN AgrBudgetContainerView ABCV on ABCV.Id = SAB.AgrBudgetContainerViewId 
	where 
		SAB.IsValid = 1 
		and SAB.IsNewBudget = 0

--Update AgrFund for Bwh FundMrn
			update f

			set fundMRN = BWH.FundMRN

			from agrfund f

			inner join

				  (select distinct s.spsfundnumber,s.fundMRN from stg_ie_agrbudget_ctl s

				  inner join agragreement a on a.id = s.agragreementid

				  inner join svcentity_fwkdomainorganization_map fdom on fdom.svcentityid = a.svcentityid and fdom.fwkorganizationrelationshiptypeid = 1

				  inner join fwkdomainorganization fdo on fdo.id = fdom.fwkdomainorganizationid

				  where fdo.institution = 'BWH') BWH

			on BWH.spsfundnumber  = f.fundnumber
			Where IsNull(BWH.FundMRN,'') <> ''

--Update fundId when FundMrn changes in AgrFund
	 update e  
  
   set agrFundId = f.Id  
  
   from PatientCareExpenseDetail e  
  
  inner join agrFund f on  e.FundMRN  = f.fundMRN
  inner join
  
      (select distinct s.spsfundnumber,s.fundMRN from stg_ie_agrbudget_ctl s  
  
      inner join agragreement a on a.id = s.agragreementid  
  
      inner join svcentity_fwkdomainorganization_map fdom on fdom.svcentityid = a.svcentityid and fdom.fwkorganizationrelationshiptypeid = 1  
  
      inner join fwkdomainorganization fdo on fdo.id = fdom.fwkdomainorganizationid  
  
      where fdo.institution = 'BWH') BWH  
     on BWH.spsfundnumber  = f.fundnumber
	where e.agrfundid <> f.id
	

	
	--RollBack TRANSACTION InjectInfoEdBudgetFeed
	COMMIT TRANSACTION InjectInfoEdBudgetFeed

	END TRY

    BEGIN CATCH
	DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

	IF XACT_STATE() <> 0 Rollback transaction InjectInfoEdBudgetFeed

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
		return 99
    END CATCH;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

